	
//列表查询
	@Override
	public PagingResult<UserOrder> comfirmOrderList(final TunnelUserParam param, final int currentUserId) {
		return this.pagings(param, new PagingCallback() {

			@Override
			public void doWithListCriteria(Criteria criteria) {
				comfirmOrderaddRestrictions(param, criteria, currentUserId);
			}
			@Override
			public void doWithCountCriteria(Criteria criteria) {
				comfirmOrderaddRestrictions(param, criteria, currentUserId);
			}
		});
	}

	@Override
	public PagingResult<UserInfo> users(final UsersParam param) {
		return this.pagings(param, new PagingCallback() {

			@Override
			public void doWithListCriteria(Criteria criteria) {
				criteria.addOrder(Order.asc("isLackCertificate"));
				criteria.addOrder(Order.asc("isInsuranceData"));
				criteria.addOrder(Order.desc("isInsuranceConfigChanged"));
				criteria.addOrder(Order.desc("isCpfConfigChanged"));
				criteria.addOrder(Order.asc("status"));
				addRestrictions(param, criteria);
			}

			private void addRestrictions(final UsersParam param, Criteria criteria) {
				criteria.add(Restrictions.eq("enabled", OnJobStatus.onJobType.value));
				final String name = param.getName();
				if (StringUtils.isNotBlank(name)) {
					criteria.add(Restrictions.ilike("name", name, MatchMode.ANYWHERE));
				}
				
				final String idCard = param.getIdCard();
				if (StringUtils.isNotBlank(idCard)) {
					criteria.add(Restrictions.ilike("idCard", idCard, MatchMode.ANYWHERE));
				}
				
				final int cityId = param.getCityId();
				if (cityId > 0) {
					criteria.add(Restrictions.eq("city.id", cityId));
				}

				final int companyId = param.getCompanyId();
				if (companyId > 0) {
					criteria.add(Restrictions.eq("company.id", companyId));
				}
				
				final int status = param.getInsuranceStatus();
				if (status > 0) {
					criteria.add(
							Restrictions.in("status", ArrayUtils.toObject(UserStatus.status(status).sameTitleValues)));
				}
			}

			@Override
			public void doWithCountCriteria(Criteria criteria) {
				addRestrictions(param, criteria);
			}
		});
	}

//查询条件
private void tunnelComfirmOrderaddRestrictions(TunnelUserParam param, DetachedCriteria criteria, int currentUserId) {
	
		criteria.createAlias("company", "company");
		criteria.add(Restrictions.eq("company.providerId", currentUserId));
		criteria.add(Restrictions.eq("status", UserOrderStatus.Created.value));
		
		final String name = param.getName();
		if (StringUtils.isNotBlank(name)) {
			criteria.add(Restrictions.ilike("name", name, MatchMode.ANYWHERE));
		}
		
		//like
		final String companyName = param.getCompanyName();
		if (StringUtils.isNotBlank(companyName)) {
			criteria.add(Restrictions.ilike("realCompanyName", companyName, MatchMode.ANYWHERE));
		}
		
		//in查询
		String[] orderIdStrs = param.getSelectedOrderIds().split(",");
        criteria.add(Restrictions.in("id", ConvertUtils.stringArrayToObjectLongArray(orderIdStrs)));

		//求和
		final ProjectionList proList = Projections.projectionList();  
		proList.add(Projections.sum("orderAmount.insuranceCompany"));
		criteria.setProjection(proList);  

		//distinct 唯一查询
		criteria.setProjection(Projections.distinct(Property.forName("cityId")));
}

//执行sql
public void updateUserOrdersByBusOrderId(final Long id, final UserOrderStatus orderState,
			final DeliveryStatus deliveryStatus) {
		// String hql = "UPDATE userOrder a, userBusinessOrder b SET
		// a.deliveryStatus = ? "
		// + "WHERE a.id = b.userOrderId AND b.businessBillId = ?";
		final String sql = "UPDATE user_order a, user_business_order b SET a.status = ?, a.delivery_status = ? "
				+ "WHERE a.id = b.user_order_id AND b.business_bill_id = ?";
		this.hibernateTemplate.execute(new HibernateCallback<Object>() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery(sql).setParameter(0, orderState.value)
						.setParameter(1, deliveryStatus.value).setParameter(2, id).executeUpdate();
			}
		});
		// return this.update(sql, state, id);
	}


